import psycopg2, pymysql
import os
import datetime
from db.db_properties import Table, TableField

from db.db_export import export_md


class DBPlatform:
    """
    数据库平台父类
    """
    # 查询数据库表的语句
    select_table_str = None
    # 查询表结构的语句
    select_filed_str = None

    def __init__(self, db_prop):
        self.db_prop = db_prop

    def create_export_file(self, file_path, file_ext):
        """
        根据传入的文件导出地址和后缀生产文件路径
        :param file_path: 文件路径
        :param file_ext: 文件扩展名
        :return: 返回可使用的最终文件全路径
        """
        if file_path is None:
            file_path = "%s(%s).%s" % (self.db_prop.host, self.db_prop.database, file_ext)
        elif not file_path.endswith('.%s' % file_ext):
            if not os.path.exists(file_path):
                try:
                    os.makedirs(file_path)
                except OSError as err:
                    print("创建文件{0}".format(err))
            if file_path.endswith(os.sep):
                # 说明传入了一个文件夹
                file_tmp_path = "%s%s(%s).%s" % (file_path, self.db_prop.host, self.db_prop.database, file_ext)
            else:
                file_tmp_path = "%s%s(%s).%s" % (file_path + os.sep, self.db_prop.host, self.db_prop.database, file_ext)

            if os.path.exists(file_tmp_path):
                file_path = "%s%s(%s)_%s.%s" % (
                    file_path + os.sep, self.db_prop.host, self.db_prop.database,
                    str(datetime.datetime.now().timestamp()),
                    file_ext)
            else:
                file_path = file_tmp_path
        return file_path

    def init_data(self):
        pass

    def export_md(self, file_path=None, export_fields=None):
        """
        导出md文档
        :param file_path: 文件路径
        :param export_fields: 自定义的导出信息
        :return:
        """
        file_path = self.create_export_file(file_path, "md")
        export_md(file_path=file_path, data=self.tables, fields=export_fields)


class PostgresqlDB(DBPlatform):
    """
    Postgresql
    """
    select_table_str = "SELECT a.relname AS name,b.description AS comment FROM pg_class a\
            LEFT OUTER JOIN pg_description b ON b.objsubid=0 AND a.oid = b.objoid\
 WHERE a.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='%s') \
   AND a.relkind='r'\
 ORDER BY a.relname "

    select_filed_str = "SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,\
               a.attnotnull AS notnull,\
               b.description AS comment\
          FROM pg_class c,\
               pg_attribute a\
               LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,\
               pg_type t\
         WHERE c.relname = '%s'\
               and a.attnum > 0\
               and a.attrelid = c.oid\
               and a.atttypid = t.oid\
         ORDER BY a.attnum"

    def __init__(self, db_prop):
        super(PostgresqlDB, self).__init__(db_prop)
        self.db = psycopg2.connect(database=db_prop.database, user=db_prop.user, password=db_prop.password,
                                   host=db_prop.host, port=db_prop.port)
        self.cursor = self.db.cursor()
        self.db_prop = db_prop
        self.tables = []

    def init_data(self):
        self.cursor.execute(
            self.select_table_str % self.db_prop.schema)
        table_names = self.cursor.fetchall()
        for table_name_t in table_names:
            table_name = table_name_t[0]
            if not self.db_prop.ignore_table_fun(table_name_t[0], table_name_t[1]):
                table = Table(table_name_t[0], table_name_t[1], [])
                self.cursor.execute(self.select_filed_str % table_name)
                table_info = self.cursor.fetchall()
                for table_field in table_info:
                    """
                        查询的参数分类边长和定长类型，边长类型比实际多了4个长度，定长类型比实际多了2个长度
                    """
                    if table_field[3] == -1:
                        # 变长参数
                        c_length = table_field[4] - 4
                    else:
                        # 定长参数
                        c_length = table_field[3] - 2
                    table.t_fields.append(
                        TableField(table_field[1], table_field[2], c_length, table_field[5], table_field[6]))
                self.tables.append(table)
        self.cursor.close()
        self.db.close()


class MysqlDB(DBPlatform):
    """
    Mysql数据库
    """
    select_table_str = "SELECT TABLE_NAME as name,TABLE_COMMENT as comment\
    FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '%s' order by TABLE_NAME ; "

    select_filed_str = "SELECT ORDINAL_POSITION as attnum,COLUMN_NAME AS field,DATA_TYPE AS type," \
                       "CHARACTER_MAXIMUM_LENGTH AS length,IS_NULLABLE AS notnull, COLUMN_COMMENT AS comment FROM " \
                       "information_schema.`COLUMNS` WHERE TABLE_SCHEMA = '%s' and table_name = '%s' ORDER BY ORDINAL_POSITION;"

    def __init__(self, db_prop):
        super(MysqlDB, self).__init__(db_prop)
        self.db = pymysql.connect(database=db_prop.database, user=db_prop.user, password=db_prop.password,
                                  host=db_prop.host, port=int(db_prop.port), charset="utf8")
        self.cursor = self.db.cursor()
        self.db_prop = db_prop
        self.tables = []

    def init_data(self):
        self.cursor.execute(
            self.select_table_str % self.db_prop.database)
        table_names = self.cursor.fetchall()
        for table_name_t in table_names:
            table_name = table_name_t[0]
            if not self.db_prop.ignore_table_fun(table_name_t[0], table_name_t[1]):
                table = Table(table_name_t[0], table_name_t[1], [])
                self.cursor.execute(self.select_filed_str % (self.db_prop.database, table_name))
                table_info = self.cursor.fetchall()
                for table_field in table_info:
                    table.t_fields.append(
                        TableField(table_field[1], table_field[2], table_field[3], table_field[4] == 'YES',
                                   table_field[5]))
                self.tables.append(table)
        self.cursor.close()
        self.db.close()
